import time
import pymysql
import datetime
import json

host = '127.0.0.1'
user = 'root'
password = ''
port = 3306
dbname = 'yiqing'
with open("./database.json", "r") as f:  # 打开文件
    data = f.read()  # 读取文件
    data = json.loads(data)
    host = data.get('host')
    user = data.get('user')
    password = data.get('password')
    port = data.get('port')
    dbname = data.get('dbname')

def get_time():
    time_str = time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年","月","日")


def get_conn():
    # 建立连接
    conn = pymysql.connect(host=host, user=user, password=password, db=dbname, charset="utf8")
    # c创建游标A
    cursor = conn.cursor()
    return conn, cursor


def close_conn(conn, cursor):
    if cursor:
        cursor.close()
    if conn:
        conn.close()

def query(sql,*args):
    """

    :param sql:
    :param args:
    :return:
    """
    conn,cursor = get_conn()
    cursor.execute(sql,args)
    res = cursor.fetchall()
    close_conn(conn,cursor)
    return res

def get_c1_data():
    sql = "SELECT * FROM total ORDER BY addtime DESC"
    res = query(sql)
    return res[0]

def get_center_data():
    sql = "SELECT * FROM diagnosis_province"
    res = query(sql)
    return res

def test():
    sql = "select * from details"
    res = query(sql)
    return res[0]


def get_c2_data():
    sql = "select province,sum(confirm) from details " \
          "where update_time=(select update_time from details " \
          "order by update_time desc limit 1) " \
          "group by province"
    res = query(sql)
    return res

def get_l1_data():
    sql = "select * from total order by addtime desc limit 5"
    res = query(sql)
    return res

def get_l2_data():
    sql = "select * from add_trend order by addtime desc limit 5"
    res = query(sql)
    return res

def get_r1_data():
    sql = "select * from diagnosis_province where province != '湖北' order by nums desc limit 5"
    res = query(sql)
    return res

def get_r2_data():
    sql = "select * from diagnosis_province where province != '湖北' order by xc desc limit 5"
    res = query(sql)
    return res

# def get_r2_data():
#     sql = "select * from article order by id desc limit 6"
#     res = query(sql)
#     return res
# def get_r2_data():
#     sql = "select content from hotsearch order by id desc limit 20"
#     res = query(sql)
#     return res

if __name__ == "__main__":
    print(get_l2_data())
    #print(test())